import sys
import os

sys.path.append(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))

from config import result_bucket
from aws_model.athena_submit import submit
from sql_lambda.delete import delete_data
from base_class.inspect_logging import print_to_log
import datetime
import json
from aws_model.aws_stepfunc import send_task_success, send_task_failure


def result_xhbll2(event):
    date = event.get('p_date')
    if not date:
        date = datetime.datetime.today().strftime('%Y-%m-%d')
    table_name = sys._getframe().f_code.co_name
    delete_data(result_bucket,table_name, date)
    sql = f'''insert into fridge_biz_result.result_xhbll2(
                    select
                        factory_table.model  as model,
                        if(error_table.error_num is null,0,error_table.error_num) as error_num,
                        factory_table.factory_num as access_num,
                        factory_table.years as years,
                        factory_table.factory_date as factory_date,
                        format_datetime((date('{date}') - interval '1' month),'yyyy-MM') as time
                    from
                        (
                        select
                            model,factory_date,factory_num,years
                        from
                        (
                            select
                                a.model,format_datetime(factory_date,'yyyy-MM') as factory_date,count(sn) as factory_num,'1年,2年,3年,其他' as y
                            from
                               (
                                 select
                                    t1.sn, upper(t3.model) as model,COALESCE(
                                            TRY(
                                                date(
                                                    concat(
                                                        t2.name,'-',
                                                        substr(t1.sn,17,2),'-',
                                                        substr(t1.sn,19,2)
                                                        )
                                                    )
                                                ),null
                                        ) factory_date
                                from
                                    ods.device t1
                                left join
                                    dim.tree_book t2
                                on
                                    upper(substr(t1.sn,16,1)) = t2.code and t2.classify_name = 'sn出厂年份'
                                left join
                                    dim.material t3
                                on
                                    substr(t1.sn,4,7) = t3.material
                                where
                                    length(sn) =24
                                and
                                    t1.type='FRIDGE'
                                and
                                    t3.model != ' '
                                and
                                    t3.model is not null
                                ) a
                            where
                                factory_date is not null
                            group by
                                a.model,format_datetime(factory_date,'yyyy-MM')
                        ) b
                        cross join
                            unnest(split(b.y,',')) as c(years)
                        ) factory_table
                    left join
                        (
                        select
                            model,factory_date,years,count(sn) as error_num
                        from
                            (
                             select
                                sn,upper(model) as model,substr(factory_date,1,7) as factory_date,
                                case
                                    when error_date is null then null
                                    when cast(substr(error_date,1,4) as int) - cast(substr(factory_date,1,4) as int) < 1 then '1年'
                                    when cast(substr(error_date,1,4) as int) - cast(substr(factory_date,1,4) as int) < 2 then '2年'
                                    when cast(substr(error_date,1,4) as int) - cast(substr(factory_date,1,4) as int) < 3 then '3年'
                                    else '其他'
                                end as years
                             from
                                (
                                select
                                    d.sn,d.model,
                                     COALESCE(
                                            TRY(
                                                    concat(
                                                        tb.name,'-',
                                                        substr(d.sn,17,2),'-',
                                                        substr(d.sn,19,2)
                                                            )
                                                ),null
                                           ) factory_date,
                                     kyd.error_date as error_date
                                from
                                    ods.device d
                                left join
                                    (
                                    select
                                        zzfld00000b as sn,
                                        min(p_date) as error_date
                                    from
                                        ods.kyd_ods t1
                                    where
                                        length(t1.zzfld00000b) = 24
                                    and
                                        substr(zzfld00000b, 4, 7) is not null
                                    and
                                        substr(zzfld00000b, 4, 7) != ''
                                    and
                                        t1.zzfld000001='QT30'
                                    and
                                        t1.zzfld00002ft != '安装'
                                    group by
                                        zzfld00000b
                                    ) kyd
                                on
                                    d.sn = kyd.sn
                                left join
                                    dim.tree_book tb
                                on
                                    upper(substr(d.sn,16,1)) = tb.code and tb.classify_name = 'sn出厂年份'
                                where
                                    length(d.sn) =24
                                and
                                    d.type='FRIDGE'
                                and
                                    d.sn is not null
                                and
                                    d.sn != ''
                                ) t2
                            ) t3
                        where
                            years is not null
                        group by model,factory_date,years
                        ) error_table
                    on
                        factory_table.model=error_table.model
                    and
                        factory_table.factory_date = error_table.factory_date
                    and
                        factory_table.years = error_table.years
                    where
                        factory_table.model is not null
                    )'''

    athena_response = submit(sql)
    print_to_log("athena_response: ", athena_response)
    output = {"msg": f"{table_name} submit ok", "Payload": {}}
    output = json.dumps(output)
    step_response = send_task_success(tasktoken, output)
    print_to_log("step_response: ", step_response)


if __name__ == '__main__':
    try:
        event = json.loads(sys.argv[1])
        tasktoken = event.get('TaskToken')
        result_xhbll2(event)
    except Exception as e:
        print_to_log("athena_submit 失败")
        print_to_log("原因： ", str(e))
        send_task_failure(str(e), f'执行{sys._getframe().f_code.co_filename}错误', tasktoken)

